package ch.sv7.tool.dbmanager.db.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;

import junit.framework.TestCase;
import ch.sv7.tool.dbmanager.db.ColumnSchema;
import ch.sv7.tool.dbmanager.db.ForeignKeySchema;
import ch.sv7.tool.dbmanager.db.IndexSchema;
import ch.sv7.tool.dbmanager.db.TableSchema;
import ch.sv7.tool.dbmanager.db.dialect.DefaultDialect;
import ch.sv7.tool.dbmanager.types.ConnectionTypesInfo;
import ch.sv7.tool.dbmanager.util.SQLHelper;

/**
 *
 * @author svt
 *
 */
public class SQLDatabaseSchemaFactoryMultiplePKTestCase extends TestCase {

	Connection conn = null;

	protected void setUp() throws Exception {
		super.setUp();
		
		Class.forName("org.hsqldb.jdbcDriver");
		conn = DriverManager.getConnection("jdbc:hsqldb:"
                + "mem:mydb",    // Memory
                "sa",                     // username
                "");                      // password
	}


	
	public void testTableSchema() throws Exception {
		SQLDatabaseSchemaFactory factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		List tables = Arrays.asList(factory.getTables());
		assertEquals(0, tables.size());
		
		SQLHelper.executeSQL("CREATE TABLE MYTABLE" 
				+ "(ID INTEGER, " 
				+ "NAME VARCHAR(20), " 
				+ "NUMBER INTEGER,"
				+ "NAME_NOT_NULL VARCHAR(20) NOT NULL,"
				+ "NAME_DEF VARCHAR(20) DEFAULT 'EMPTY' NOT NULL,"
				+ "PRIMARY KEY (ID, NAME))", conn);
		
		SQLHelper.executeSQL("CREATE INDEX MY_INDEX ON MYTABLE (NAME, NUMBER)", conn);
		
		factory = new SQLDatabaseSchemaFactory(conn, new DefaultDialect(new ConnectionTypesInfo(conn)));
		tables = Arrays.asList(factory.getTables());
		assertEquals(1, tables.size());
		tables.contains("mytable");

		TableSchema ts = factory.getTableSchema("MYTABLE");
	
		assertNotNull(ts);
		assertEquals("MYTABLE", ts.getName());
		/*
		 * Basic checks
		 */
		ColumnSchema cs0 = ts.getColumnSchema("ID");
		assertNotNull(cs0);
		assertEquals(Types.INTEGER, cs0.getType().getJDBCType());
		assertEquals(0, cs0.getType().getSize());		
		assertEquals(true, cs0.isPrimaryKey());		

		ColumnSchema cs1 = ts.getColumnSchema("NAME");
		assertNotNull(cs1);
		assertEquals(Types.VARCHAR, cs1.getType().getJDBCType());
		assertEquals(20, cs1.getType().getSize());
		assertEquals(false, cs1.getType().isNullable());
		assertEquals(null, cs1.getType().getDefaultValue());
		assertEquals(true, cs1.isPrimaryKey());		

		/*
		 * Index checks
		 */
		List indexes = ts.getIndexes();
		assertNotNull(indexes);
		assertEquals(2, indexes.size()); // PK + MY_INDEX
		
		IndexSchema index = (IndexSchema)indexes.get(0);
		assertNotNull(index);
		assertEquals("ID, NAME", index.getColumns());
		
		index = (IndexSchema)indexes.get(1);
		assertNotNull(index);
		assertEquals("MY_INDEX", index.getName());
		assertEquals("NAME, NUMBER", index.getColumns());
		
		/*
		 * Foreign key checks
		 */
		SQLHelper.executeSQL("CREATE TABLE MYTABLEREF" 
				+ "(ID INTEGER PRIMARY KEY, " 
				+ "REF_ID INTEGER,"
				+ "REF_NAME VARCHAR(20),"
				+ "CONSTRAINT MY_REF FOREIGN KEY (REF_ID, REF_NAME) REFERENCES MYTABLE (ID, NAME)" 
				+ ")", conn);
		ts = factory.getTableSchema("MYTABLE");
		assertEquals(0, ts.getForeignKeys().size());
		TableSchema tsRef = factory.getTableSchema("MYTABLEREF");
		assertEquals(1, tsRef.getForeignKeys().size());
		ForeignKeySchema fk = (ForeignKeySchema)tsRef.getForeignKeys().get(0);
		assertNotNull(fk);
		assertEquals("MY_REF", fk.getName());
		assertEquals("MYTABLE", fk.getForeignTable());
		assertEquals("ID, NAME", fk.getForeignColumns());
		
		SQLHelper.executeSQL("DROP table MYTABLEREF", conn);
		SQLHelper.executeSQL("DROP table MYTABLE", conn);

	};
	
	
	
	
	protected void tearDown() throws Exception {
		if (conn != null) {
			conn.close();
		}
		super.tearDown();
	}

	
	
}
